package servlets;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class PatientDAO {

	private static final String CREATE_PATIENT = "INSERT INTO qdlc_patient (name, gender, occupation, address, status,physician, dateOfBirth, dateOfRegistration, medicalHistory) VALUES (?,?,?,?,?,?,?,?,?);";
	private static final String UPDATE_PATIENT = "UPDATE qdlc_patient SET name = ?, gender = ?, occupation = ?, address = ?, status = ?, physician = ?, medicalHistory = ? WHERE patientId = ?";
	private static final String GET_PATIENT_FROM_ID = "SELECT * FROM qdlc_patient WHERE patientId = ?;";
	private static final String GET_ALL_PATIENT = "SELECT * FROM qdlc_patient;";

	private SimpleDateFormat formatter = new SimpleDateFormat(
			"yyyy-MM-dd HH:mm:ss");

	public Patient createPatient(String name, String gender, String occupation,
			String address, int status, String physician, Date dateOfBirth,
			Date dateOfRegistration, String medicalHistory) {
		Patient patient = null;

		Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

		// Build query
		try {
			PreparedStatement st = conn.prepareStatement(CREATE_PATIENT,
					PreparedStatement.RETURN_GENERATED_KEYS);

			st.setString(1, name);
			st.setString(2, gender);
			st.setString(3, occupation);
			st.setString(4, address);
			st.setInt(5, status);
			st.setString(6, physician);
			st.setString(7, formatter.format(dateOfBirth));
			st.setString(8, formatter.format(dateOfRegistration));
			st.setString(9, medicalHistory);

			int affectedRows = st.executeUpdate();

			// Don't attempt to get keys if there is no affected row
			if (affectedRows > 0) {
				ResultSet keys = st.getGeneratedKeys();

				// Get key, return newUser
				if (keys.next()) {
					int patientId = keys.getInt(1);

					patient = new Patient();
					patient.setAddress(address);
					patient.setPatientId(patientId);
					patient.setDateOfBirth(dateOfBirth);
					patient.setDateOfRegistration(dateOfRegistration);
					patient.setGender(gender);
					patient.setMedicalHistory(medicalHistory);
					patient.setPhysician(physician);
					patient.setStatus(status);
					patient.setOccupation(occupation);
					patient.setName(name);

					if (keys != null) {
						keys.close();
					}
				}
			}

			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return patient;
	}

	public void updateUser(String name, String gender, String occupation,
			String address, int status, String physician,
			String medicalHistory, int patientId) {
		Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

		try {
			PreparedStatement st = conn.prepareStatement(UPDATE_PATIENT);
			st.setString(1, name);
			st.setString(2, gender);
			st.setString(3, occupation);
			st.setString(4, address);
			st.setInt(5, status);
			st.setString(6, physician);
			st.setString(7, medicalHistory);
			st.setInt(8, patientId);

			st.executeUpdate();

			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public Patient getPatient(int patientId) {
		Patient patient = null;

		// TODO: Add exceptions
		Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

		// Build query
		try {
			PreparedStatement st = conn.prepareStatement(GET_PATIENT_FROM_ID);

			st.setInt(1, patientId);

			ResultSet rs = st.executeQuery();

			// Found result!
			if (rs.next()) {
				patient = new Patient();
				patient.setPatientId(rs.getInt("patientId"));
				patient.setAddress(rs.getString("address"));
				patient.setDateOfBirth(formatter.parse(rs
						.getString("dateOfBirth")));
				patient.setDateOfRegistration(formatter.parse(rs
						.getString("dateOfRegistration")));
				patient.setGender(rs.getString("gender"));
				patient.setMedicalHistory(rs.getString("medicalHistory"));
				patient.setName(rs.getString("name"));
				patient.setOccupation(rs.getString("occupation"));
				patient.setPhysician(rs.getString("physician"));
				patient.setStatus(rs.getInt("status"));
			}

			if (rs != null) {
				rs.close();
			}

			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return patient;
	}

	public ArrayList<Patient> getAllPatient() {
		ArrayList<Patient> patientList = new ArrayList<Patient>();
		Connection conn = DBConnectionFactoryImpl.getInstance().getConnection();

		// Build query
		try {
			PreparedStatement st = conn.prepareStatement(GET_ALL_PATIENT);

			ResultSet rs = st.executeQuery();

			// Found result!
			while (rs.next()) {
				Patient patient = new Patient();
				patient.setPatientId(rs.getInt("patientId"));
				patient.setAddress(rs.getString("address"));
				patient.setDateOfBirth(formatter.parse(rs
						.getString("dateOfBirth")));
				patient.setDateOfRegistration(formatter.parse(rs
						.getString("dateOfRegistration")));
				patient.setGender(rs.getString("gender"));
				patient.setMedicalHistory(rs.getString("medicalHistory"));
				patient.setName(rs.getString("name"));
				patient.setOccupation(rs.getString("occupation"));
				patient.setPhysician(rs.getString("physician"));
				patient.setStatus(rs.getInt("status"));

				patientList.add(patient);
			}

			if (rs != null) {
				rs.close();
			}

			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return patientList;

	}

}
